﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class FuelMonitoringObject
    {
        public const string sql = @"
SELECT CONVERT(nchar(5),r.[Id]) as ID
      ,CONVERT(nchar(20),[DictionaryMachines].[Name]) as Машина
      ,CONVERT(nchar(10), r.[Date], 104) + ' ' +CONVERT(nchar(5), r.[Date], 108) as Дата
      ,CONVERT(nchar(20),[DictionaryPartners].[Name]) as Поставщик
      ,CONVERT(nchar(10),[DictionaryFuelType].[Name]) as Топливо
      ,CONVERT(nchar(20),[DictionaryDrivers].[Name]) as Водитель
      ,CONVERT(nchar(8),COALESCE(r.[FuelRemaining],0)) as Остаток__в__баке
      ,CONVERT(nchar(8),COALESCE(r.[Liters],0)) as Заправлено__л
      ,CONVERT(nchar(8),COALESCE(r.[Price],0)) as Заправлено__грн
      ,CONVERT(nchar(8),COALESCE(r.[Distance],0)) as Пробег
      ,CONVERT(nchar(8),COALESCE((round(((((r2.FuelRemaining + r2.Liters) - r.FuelRemaining) * 100) / r.Distance),2)),0)) as Расход
      ,CONVERT(nchar(8),COALESCE(r.[Odometer],0)) as Одометр
      ,CONVERT(nchar(8),COALESCE((r.Odometer - r2.Odometer),0)) as Пробег__одометр
      ,CONVERT(nchar(5),r2.[Id]) as ID__прошл__запр
      ,CONVERT(nchar(10), r2.[Date], 104) + ' ' +CONVERT(nchar(5), r2.[Date], 108) as Дата__прошл__запр
      --,CONVERT(nchar(8),COALESCE((r2.FuelRemaining + r2.Liters),0)) as Ост__прошл__запр --Прошлый остаток в банке + прошлая заправка
      ,CONVERT(nchar(10),[DictionaryProject].[Name]) as Проект
  FROM [Refueling] as r
  LEFT JOIN DictionaryPartners on r.FuelSupplier = DictionaryPartners.ID
  LEFT JOIN DictionaryFuelType on r.FuelType = DictionaryFuelType.ID
  LEFT JOIN DictionaryMachines on r.Machine = DictionaryMachines.ID
  LEFT JOIN DictionaryDrivers on r.Driver = DictionaryDrivers.ID
  LEFT JOIN DictionaryProject on r.Project = DictionaryProject.ID
  LEFT JOIN Refueling as r2 on 
		r.Machine = r2.Machine and 
		r2.FuelOperType = 2 and
		r2.date in (select max(r3.date)
					from Refueling as r3 
					where r3.date < r.date and r3.FuelOperType = 2 and r.Machine = r3.Machine) 
WHERE r.[FuelOperType] = 2
ORDER BY r.[Date], r.[Machine]
";
        public string ID { get; set; }
        public string Машина { get; set; }
        public string Дата { get; set; }
        public string Поставщик { get; set; }
        public string Топливо { get; set; }
        public string Водитель { get; set; }
        public string Заправлено__л { get; set; }
        public string Заправлено__грн { get; set; }
        public string Остаток__в__баке { get; set; }
        public string Пробег { get; set; }
        public string Расход { get; set; }
        public string Одометр { get; set; }
        public string Пробег__одометр { get; set; }
        public string ID__прошл__запр { get; set; }
        public string Дата__прошл__запр { get; set; }
        //public string Ост__прошл__запр { get; set; }
        public string Проект { get; set; }
    }
}

//Версия отчета до 05.10.2016 - время выполнения - 6 секунд. Все поля string
//SELECT CONVERT(nchar(5),r.[Id]) as ID
//      ,CONVERT(nchar(20),[DictionaryMachines].[Name]) as Машина
//      ,CONVERT(nchar(10), r.[Date], 104) + ' ' +CONVERT(nchar(5), r.[Date], 108) as Дата
//      ,CONVERT(nchar(20),[DictionaryPartners].[Name]) as Поставщик
//      ,CONVERT(nchar(10),[DictionaryFuelType].[Name]) as Топливо
//      ,CONVERT(nchar(20),[DictionaryDrivers].[Name]) as Водитель
//      ,CONVERT(nchar(8),COALESCE(r.[FuelRemaining],0)) as Остаток__в__баке
//      ,CONVERT(nchar(8),COALESCE(r.[Liters],0)) as Заправлено__л
//      ,CONVERT(nchar(8),COALESCE(r.[Price],0)) as Заправлено__грн
//      ,CONVERT(nchar(8),COALESCE(r.[Distance],0)) as Пробег
//      ,CONVERT(nchar(8),COALESCE((round(((((r2.FuelRemaining + r2.Liters) - r.FuelRemaining) * 100) / r.Distance),2)),0)) as Расход
//      ,CONVERT(nchar(8),COALESCE(r.[Odometer],0)) as Одометр
//      ,CONVERT(nchar(8),COALESCE((r.Odometer - r2.Odometer),0)) as Пробег__одометр
//      ,CONVERT(nchar(5),r2.[Id]) as ID__прошл__запр
//      ,CONVERT(nchar(10), r2.[Date], 104) + ' ' +CONVERT(nchar(5), r2.[Date], 108) as Дата__прошл__запр
//      --,CONVERT(nchar(8),COALESCE((r2.FuelRemaining + r2.Liters),0)) as Ост__прошл__запр --Прошлый остаток в банке + прошлая заправка
//      ,CONVERT(nchar(10),[DictionaryProject].[Name]) as Проект
//  FROM [Refueling] as r
//  LEFT JOIN DictionaryPartners on r.FuelSupplier = DictionaryPartners.ID
//  LEFT JOIN DictionaryFuelType on r.FuelType = DictionaryFuelType.ID
//  LEFT JOIN DictionaryMachines on r.Machine = DictionaryMachines.ID
//  LEFT JOIN DictionaryDrivers on r.Driver = DictionaryDrivers.ID
//  LEFT JOIN DictionaryProject on r.Project = DictionaryProject.ID
//  LEFT JOIN Refueling as r2 on 
//        r.Machine = r2.Machine and 
//        r2.FuelOperType = 2 and
//        r2.date in (select max(r3.date)
//                    from Refueling as r3 
//                    where r3.date < r.date and r3.FuelOperType = 2 and r.Machine = r3.Machine) 
//WHERE r.[FuelOperType] = 2
//ORDER BY r.[Date], r.[Machine]
